﻿/*
	AnOtherBlog 0.3 SQL Server 2005 SetUp Script
	
	Log: (fecha, autor, comentario)
	2008-07-02, Julio, Script para crear el esquema en la base de datos
	2008-07-03, Julio, Ajustes varios
					aob_Pages: agrega Slugs
					aob_Posts: suprime Raters y Rating
					aob_Comments: cambia el nombre CommentContent
	2008-07-05, Julio, Ajustes varios
					aob_Blogs: Tabla para mantener la Configuración del Blog
					aob_Categories, aob_Pages, aob_Posts: Agrega BlogID
*/

/* ¡¡¡ CUIDADO !!! esto se realiza sobre esta base de datos */
USE [AnOtherBlog]
GO


/* Cateogorías habilitadas */
/* Object:  Table [dbo].[aob_Categories] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_Categories](
	[CategoryID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_aob_Categories_CategoryID]  DEFAULT (newid()),
	[CategoryName] [nvarchar](50) NULL,
	[Description] [nvarchar](200) NULL,
	[BlogID] [int] NOT NULL CONSTRAINT [DF_aob_Categories_BlogID]  DEFAULT ((1)),
 CONSTRAINT [PK_aob_Categories] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


/* Páginas para mostrar información estática */
/* Object:  Table [dbo].[aob_Pages] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_Pages](
	[PageID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_aob_Pages_PageID]  DEFAULT (newid()),
	[Title] [nvarchar](255) NULL,
	[Description] [nvarchar](max) NULL,
	[PageContent] [nvarchar](max) NULL,
	[Keywords] [nvarchar](max) NULL,
	[DateCreated] [datetime] NULL,
	[DateModified] [datetime] NULL,
	[IsPublished] [bit] NULL,
	[IsFrontPage] [bit] NULL,
	[Parent] [uniqueidentifier] NULL,
	[ShowInList] [bit] NULL,
	[Slug] [nvarchar](255) NULL,
	[BlogID] [int] NOT NULL CONSTRAINT [DF_aob_Pages_BlogID]  DEFAULT ((1)),
 CONSTRAINT [PK_aob_Pages] PRIMARY KEY CLUSTERED 
(
	[PageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


/* Post - Publicaciones (el objeto del proyecto!!!) */
/* Object:  Table [dbo].[aob_Posts] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_Posts](
	[PostID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_aob_Posts_PostID]  DEFAULT (newid()),
	[Title] [nvarchar](255) NULL,
	[Description] [nvarchar](max) NULL,
	[PostContent] [nvarchar](max) NULL,
	[DateCreated] [datetime] NULL,
	[DateModified] [datetime] NULL,
	[Author] [nvarchar](50) NULL,
	[IsPublished] [bit] NULL,
	[IsCommentEnabled] [bit] NULL,
	[Slug] [nvarchar](255) NULL,
	[BlogID] [int] NOT NULL CONSTRAINT [DF_aob_Posts_BlogID]  DEFAULT ((1)),
 CONSTRAINT [PK_aob_Posts] PRIMARY KEY CLUSTERED 
(
	[PostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


/* Version - Release de la base de datos y configuración (settings) de la aplicación */
/* Objeto:  Table [dbo].[aob_Versions] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_Versions](
	[VersionID] [int] IDENTITY(1,1) NOT NULL,
	[VersionRelease] [nvarchar](255) NOT NULL,
	[VersionSettings] [xml] NULL,
	[Comments] [nvarchar](max) NULL,
 CONSTRAINT [PK_aob_Versions] PRIMARY KEY CLUSTERED 
(
	[VersionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


/* Configuración del Blog */
/* Objeto:  Table [dbo].[aob_Blogs] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_Blogs](
	[BlogID] [int] NOT NULL,
	[BlogDomain] [nvarchar](255) NULL,
	[BlogSettings] [xml] NULL,
 CONSTRAINT [PK_aob_Blogs] PRIMARY KEY CLUSTERED 
(
	[BlogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



/* Relación muchos-muchos entre Posts y Categorías */
/* Object:  Table [dbo].[aob_PostCategory] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_PostCategory](
	[PostCategoryID] [int] IDENTITY(1,1) NOT NULL,
	[PostID] [uniqueidentifier] NOT NULL,
	[CategoryID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_aob_PostCategory] PRIMARY KEY CLUSTERED 
(
	[PostCategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[aob_PostCategory]  WITH CHECK ADD  CONSTRAINT [FK_aob_PostCategory_aob_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[aob_Categories] ([CategoryID])
GO
ALTER TABLE [dbo].[aob_PostCategory] CHECK CONSTRAINT [FK_aob_PostCategory_aob_Categories]
GO
ALTER TABLE [dbo].[aob_PostCategory]  WITH CHECK ADD  CONSTRAINT [FK_aob_PostCategory_aob_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[aob_Posts] ([PostID])
GO
ALTER TABLE [dbo].[aob_PostCategory] CHECK CONSTRAINT [FK_aob_PostCategory_aob_Posts]
GO


/* Comentarios añadidos a un Post */
/* Object:  Table [dbo].[aob_PostComment] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_PostComment](
	[PostCommentID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_aob_PostComment_PostCommentID]  DEFAULT (newid()),
	[PostID] [uniqueidentifier] NOT NULL,
	[CommentDate] [datetime] NOT NULL,
	[Author] [nvarchar](255) NULL,
	[Email] [nvarchar](255) NULL,
	[Website] [nvarchar](255) NULL,
	[CommentContent] [nvarchar](max) NULL,
	[Country] [nvarchar](255) NULL,
	[Ip] [nvarchar](50) NULL,
	[IsApproved] [bit] NULL,
 CONSTRAINT [PK_aob_PostComment] PRIMARY KEY CLUSTERED 
(
	[PostCommentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[aob_PostComment]  WITH CHECK ADD  CONSTRAINT [FK_aob_PostComment_aob_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[aob_Posts] ([PostID])
GO
ALTER TABLE [dbo].[aob_PostComment] CHECK CONSTRAINT [FK_aob_PostComment_aob_Posts]
GO

/* Notificaciones añadidas a un Post */
/* Object:  Table [dbo].[aob_PostNotify] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_PostNotify](
	[PostNotifyID] [int] IDENTITY(1,1) NOT NULL,
	[PostID] [uniqueidentifier] NOT NULL,
	[NotifyAddress] [nvarchar](255) NULL,
 CONSTRAINT [PK_aob_PostNotify] PRIMARY KEY CLUSTERED 
(
	[PostNotifyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[aob_PostNotify]  WITH CHECK ADD  CONSTRAINT [FK_aob_PostNotify_aob_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[aob_Posts] ([PostID])
GO
ALTER TABLE [dbo].[aob_PostNotify] CHECK CONSTRAINT [FK_aob_PostNotify_aob_Posts]
GO



/* Marcas añadidas a un Post */
/* Object:  Table [dbo].[aob_PostTag] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aob_PostTag](
	[PostTagID] [int] IDENTITY(1,1) NOT NULL,
	[PostID] [uniqueidentifier] NOT NULL,
	[Tag] [nvarchar](50) NULL,
 CONSTRAINT [PK_aob_PostTag] PRIMARY KEY CLUSTERED 
(
	[PostTagID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[aob_PostTag]  WITH CHECK ADD  CONSTRAINT [FK_aob_PostTag_aob_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[aob_Posts] ([PostID])
GO
ALTER TABLE [dbo].[aob_PostTag] CHECK CONSTRAINT [FK_aob_PostTag_aob_Posts]
GO



/* Indices para mejorar el acceso */

/* Facilita el acceso a las categorías de un post */
/* Object:  Index [FK_PostID] */
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[aob_PostCategory] 
(
	[PostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/* Facilita el acceso a los post de una categoría */
/* Object:  Index [FK_CategoryID] */
CREATE NONCLUSTERED INDEX [FK_CategoryID] ON [dbo].[aob_PostCategory] 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/* Facilita el acceso a los comentarios de un post */
/* Object:  Index [FK_PostID] */
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[aob_PostComment] 
(
	[PostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/* Facilita el acceso a las notificaciones de un post */
/* Object:  Index [FK_PostID] */
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[aob_PostNotify] 
(
	[PostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/* Facilita el acceso a las marcas de un post */
/* Object:  Index [FK_PostID] */
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[aob_PostTag] 
(
	[PostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


